package handler;


import DataBase.DBMsg;
import DataBase.myDB;
import com.example.banksys.*;
import javafx.collections.ObservableList;

import javafx.scene.control.ComboBox;
import javafx.scene.control.TextField;

import java.nio.Buffer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import static DataBase.DBMsg.State.ERROR;
import static DataBase.DBMsg.State.SUCCESS;

public class queryHandler {
    private  myDB database;

    public queryHandler(){
        database =new myDB("banksystem");
    }

    public queryHandler(String USER){
        database=new myDB("banksystem",USER);
    }

    //下方是查询方法，对应着每一种角色的查询请求，需要能够返回各种错误代码，或者是将传入参数附上结果

    public DBMsg accountQuery(Account account) throws SQLException {
        String sql="select * from banksystem.customer_account where account_ID="+account.getAccount_ID();
        ResultSet resultSet = database.getStatement().executeQuery(sql);
        if(resultSet.next()){
            if(!account.getPassword().equals(resultSet.getString("password"))) {
                resultSet.close();
                return new DBMsg(ERROR,"账号或密码错误！");
            }
            else{
                    account.setAccount_ID(resultSet.getLong("account_ID"));
                    account.setCustomer_ID(resultSet.getLong("customer_ID"));
                    sql="select * from sub_account where account_ID = "+account.getAccount_ID()+" and type_ID=2";
                    myDB inner=new myDB("banksystem");
                    ResultSet res=inner.getStatement().executeQuery(sql);
                    res.next();
                    account.setBalance(res.getDouble("sub_balance"));
                    account.setType(resultSet.getString("type"));
                    account.setOpen_time(resultSet.getTimestamp("open_time"));
                resultSet.close();
                return new DBMsg(SUCCESS,"查询成功！");
            }

        } else{
            resultSet.close();
            return new DBMsg(ERROR,"未查询到该账号信息！");
        }
    }

    public DBMsg tradeQuery(Account account, ObservableList<tradeTrace> tradeTraceObservableList) throws SQLException {
        String sql="select * from banksystem.trade_record where account_ID="+account.getAccount_ID()+" order by trade_date desc limit 0,200";//倒叙查看最近200条记录
        ResultSet resultSet = database.getStatement().executeQuery(sql);
        int count=0;
        while(resultSet.next()){
            tradeTrace t=new tradeTrace();
            t.setTrade_ID(resultSet.getLong("trade_ID"));
            t.setMoney(resultSet.getDouble("money"));
            t.setTrade_date(resultSet.getTimestamp("trade_date"));
            t.setTeller_ID(resultSet.getLong("teller_ID"));
            long typeid=resultSet.getLong("type_ID");
            sql="select * from banksystem.trade_type where type_ID="+typeid;
            ResultSet resultSet1=new myDB("banksystem").getStatement().executeQuery(sql);
            resultSet1.next();
            t.setType(resultSet1.getString("type_name"));
            tradeTraceObservableList.add(t);
            count++;
        }
        if(tradeTraceObservableList.isEmpty())
            return new DBMsg(ERROR,"未查询到相关记录！");
        else return new DBMsg(SUCCESS,"查询成功，共有"+count+"条记录");
    }

    public DBMsg employeeLogin(long id,String password) throws SQLException {
        boolean isteller=false;
        String sql="select * from employee where employee_ID ="+id;
        ResultSet resultSet=database.getStatement().executeQuery(sql);
        if(!resultSet.next())
            return new DBMsg(ERROR,"ID输入错误！");
        else{
            if(!password.equals(resultSet.getString("password")))
                return new DBMsg(ERROR,"密码错误！");
            else{
                MainApplication.employeeID=id;
                if(resultSet.getString("type").equals("业务人员")){
                    isteller=true;
                    String anothersql="select * from employee_teller where employee_ID ="+Long.parseLong(resultSet.getString("employee_ID"));
                    myDB newDB=new myDB("banksystem");
                    ResultSet t=newDB.getStatement().executeQuery(anothersql);
                    if(t.next())
                        MainApplication.tellerID=t.getLong("teller_ID");
                }
                MainApplication.isteller=isteller;
                PreparedStatement t=database.getConnection().prepareStatement("insert into log values (?,?)");
                t.setLong(1,MainApplication.employeeID);
                t.setTimestamp(2,new Timestamp(System.currentTimeMillis()));
                t.execute();
                return new DBMsg(SUCCESS,"登陆成功！");
            }
        }
    }

    public DBMsg extensiveEmployeeTradeQuery(TextField id, ComboBox<String> Organization,ObservableList<employeeTradeTrace> tradeTraceObservableList) throws SQLException {
        myDB database=new myDB("banksystem");
        myDB database2=new myDB("banksystem");
        StringBuilder sql=new StringBuilder("select * from trade_record,trade_type where trade_record.type_ID=trade_type.type_ID");
        if(!(id.getText().isEmpty()|id.getText()==null))
            sql.append(" and trade_record.teller_ID = ").append(id.getText());
        if(!(Organization.getValue()==null||Organization.getValue().equals("all"))){
            sql.append(" and trade_record.teller_ID in (select teller_ID from employee,employee_teller where employee.employee_ID=employee_teller.employee_ID and employee.organization = '").append(Organization.getValue()).append("')");
        }
        sql.append(" order by trade_date desc limit 0,200");
        ResultSet reslist=database.getStatement().executeQuery(sql.toString());
        while(reslist.next()){
            employeeTradeTrace currentTrace=new employeeTradeTrace();
            currentTrace.setTradeID(reslist.getLong("trade_record.trade_ID"));
            currentTrace.setTradeType(reslist.getString("trade_type.type_name"));
            currentTrace.setTradeMoney(reslist.getDouble("trade_record.money"));
            currentTrace.setTradeDate(reslist.getTimestamp("trade_record.trade_date"));
            currentTrace.setTellerID(reslist.getLong("trade_record.teller_ID"));
            String innersql="select * from employee,employee_teller where employee.employee_ID=employee_teller.employee_ID and employee_teller.teller_ID="+reslist.getLong("trade_record.teller_ID");
            ResultSet empRes=database2.getStatement().executeQuery(innersql);
            empRes.next();
            currentTrace.setTellerName(empRes.getString("employee.name"));
            currentTrace.setTellerOrganization(empRes.getString("employee.organization"));
            tradeTraceObservableList.add(currentTrace);
        }
        return new DBMsg(SUCCESS,"ojbk");


    }


    public DBMsg tradeItemsQuery(ComboBox<String> typeChoose, ObservableList<tradeItem> tradeItemObservableList) throws SQLException {
        myDB database=new myDB("banksystem");
        StringBuilder sql=new StringBuilder("select * from trade_type where not type_category='interest'");
        if(!(typeChoose.getValue() == null||typeChoose.getValue().equals("all"))){
            sql.append(" and type_category='").append(typeChoose.getValue()).append("'");
        }
        ResultSet tradeRes=database.getStatement().executeQuery(String.valueOf(sql));
        while(tradeRes.next()){
            tradeItem currItem=new tradeItem();
            currItem.setTradeID(tradeRes.getLong("type_ID"));
            currItem.setTradeType(tradeRes.getString("type_category"));
            currItem.setTradeName(tradeRes.getString("type_name"));
            currItem.setTradeDescription(tradeRes.getString("describe"));
            currItem.setTradeDuration(tradeRes.getString("duration"));
            tradeItemObservableList.add(currItem);
        }

        return new DBMsg(SUCCESS,"ojbk");

    }

    public DBMsg accountManageQuery(TextField identity,ObservableList<Account> accountObservableList) throws SQLException {
        accountObservableList.remove(0,accountObservableList.size());
        if(identity.getText().equals("")||identity.getText().isEmpty()){
            return new DBMsg(ERROR,"请输入客户身份证号！");
        }
        long identityID=Long.parseLong(identity.getText());
        database=new myDB("banksystem");
        String sql="select * from customer_account natural join identity_customer where identity_ID = "+identityID;
        ResultSet res=database.getStatement().executeQuery(sql);
        while(res.next()){
            Account curr=new Account();
            curr.setAccount_ID(res.getLong("account_ID"));
            String inner="select * from sub_account where account_ID = "+curr.getAccount_ID()+" and type_ID=2";
            myDB innerDB=new myDB("banksystem");
            ResultSet res1=innerDB.getStatement().executeQuery(inner);
            res1.next();
            curr.setBalance(res1.getDouble("sub_balance"));
            curr.setOpen_time(res.getTimestamp("open_time"));
            curr.setType(res.getString("type"));
            accountObservableList.add(curr);
        }
        return new DBMsg(SUCCESS,"查询成功！");

    }
}
